<cfcomponent output="false">
	
	<!--- --->

	<cffunction name="getDiscussions" access="public" output="true" returntype="query">
		<cfargument name="userID" required="false" default="#request.session.user.id#" />
		<cfargument name="discussionCategoryID" required="false" default="" />
		<cfargument name="order" required="false" default="discussion.created desc">
		<cfargument name="followers" required="false" default="" />
		<cfargument name="checked_forum" required="false" default="" />
		<cfargument name="search" required="false" default="" />
		<cfargument name="archive" required="false" default="" />

		<cfset var local = {} />

		<cfquery name="local.getDiscussions" datasource="#$.site.getVar('datasource')#">
			select 
				hex(discussion.id) as id,
				discussion.name,
				discussion_category.name as discussion_category,
				discussion.id_number,
				date_format(discussion.created,'%m/%d/%Y %H:%i:%s') as created,
				ifNull(message_count.unread_count,0) as unread,
				ifNull(message_count.message_count,0) as messages,
				if(discussion_user.id is null,0,1) as follow,
				ifNull(follower_count.counts,0) as other_followers,
				follower_count.followers,
				date_format(message_date.max_date,'%m/%d/%Y %H:%i:%s') as unread_date,
				discussion.archive
			from discussion
			inner join discussion_category on discussion_category.id = discussion.discussion_category_id
			<cfif arguments.userID neq "">inner<cfelse>left</cfif> join discussion_user on discussion_user.discussion_id = discussion.id
				and discussion_user.active = 1
				and discussion_user.user_id = #$.database.uuid(request.session.user.id)#
			left join (
				select discussion_message.discussion_id as discussion_id,
				sum(case when discussion_message_user_read.id is null then 1 else 0 end) as unread_count,
				count(discussion_message.id) as message_count,
				count(discussion_message.id) as counts
				from discussion_message
				left join discussion_message_user_read on discussion_message_user_read.discussion_message_id = discussion_message.id
					and discussion_message_user_read.active = 1
					and discussion_message_user_read.user_id = #$.database.uuid(arguments.userID)#
				where discussion_message.active = 1
				and discussion_message_user_read.id is null
				group by discussion_message.discussion_id
			) as message_count on message_count.discussion_id = discussion.id
			left join (
				select 
				discussion_message.discussion_id,
				max(discussion_message.created) max_date
				from discussion_message
				group by discussion_message.discussion_id
			) as message_date on message_date.discussion_id = discussion.id
			left join (
				select discussion_user.discussion_id,
				count(discussion_user.id) as counts,
				group_concat(concat(concat(user.first_name,' @ '),ifNull(business.name,'unnamed business')),'|') as followers
				from discussion_user
				inner join user on user.id = discussion_user.user_id
				left join business on business.id = user.business_id
					and business.active = 1
				where discussion_user.active = 1
				and discussion_user.user_id != #$.database.uuid(arguments.userID)#
				group by discussion_user.discussion_id
			) as follower_count on follower_count.discussion_id = discussion.id
			where discussion.active = 1
			<cfif arguments.discussionCategoryID neq "">
				and discussion.discussion_category_id = #$.database.uuid(arguments.discussionCategoryID)#
			</cfif>
			<cfif arguments.followers neq "">
				and ifNull(follower_count.counts,0) > 0
			</cfif>
			<cfif arguments.checked_forum neq ''>
				and discussion.created > #$.database.string(arguments.checked_forum)#
			</cfif>
			<cfif arguments.search neq "">
				and (
					discussion.id_number = #$.database.string(arguments.search)#
					or follower_count.followers like '%#arguments.search#%'
				)
			</cfif>
			<cfif request.session.user.admin> <!--- admins don't want to see messages --->
				and discussion.archive = #$.database.string(0)#
			</cfif>
			order by #arguments.order#
		</cfquery>

		
		<cfreturn local.getDiscussions />
		
	</cffunction>
	
	<!--- --->
	
	<cffunction name="getMessages" access="public" output="true" returntype="query">
		<cfargument name="discussionID" required="false" default="" />

		<cfset var local = {} />

		<cfquery name="local.getMessages" datasource="#$.site.getVar('datasource')#">
			select 
			hex(discussion_message.id) as id,
			hex(discussion_message.discussion_id) as discussion_id,
			discussion_message.message,
			date_format(discussion_message.created,'%m/%d/%Y %H:%i:%s') as created,
			hex(user.id) as user_id,
			user.first_name as name,
			business.name as business_name,
			hex(discussion_message_user_read.id) as readID
			from discussion_message
			inner join user on user.id = discussion_message.user_id
			left join business on business.id = user.business_id
				and business.active = 1
			left join discussion_message_user_read on 
				discussion_message_user_read.discussion_message_id = discussion_message.id
				and discussion_message_user_read.active = 1
				and discussion_message_user_read.user_id = #$.database.uuid(request.session.user.id)#
			where discussion_message.active = 1
			<cfif arguments.discussionID neq "">
				and discussion_message.discussion_id = #$.database.uuid(arguments.discussionID)#
			</cfif>
			order by discussion_message.created desc
		</cfquery>
		
		<cfreturn local.getMessages />
		
	</cffunction>
	
	<!--- --->
	
	<cffunction name="getDiscussionCategories" access="public" output="true" returntype="query">
		<cfargument name="widget" required="false" default="" />

		<cfset var local = {} />

		<cfquery name="local.getDiscussionCategories" datasource="#$.site.getVar('datasource')#">
			select 
			hex(discussion_category.id) as id,
			discussion_category.name,
			discussion_category.sort_order
			from discussion_category
			where discussion_category.active = 1
			<cfif arguments.widget neq "">
				and discussion_category.widget = #$.database.string(arguments.widget)#
			</cfif>
			order by widget desc, sort_order asc
		</cfquery>
		
		<cfreturn local.getDiscussionCategories />
		
	</cffunction>
	
	<!--- --->
	
	<cffunction name="getSimilarDiscussions" access="public" output="true" returntype="query">
		<cfargument name="okWords" required="true" />
		<cfargument name="discussionCategoryID" required="true" />

		<cfset var local = {} />
		
		<cfset local.denom = structCount(arguments.okWords) />
		<cfif local.denom eq 0>
			<cfset local.denom = .01 />
		</cfif>

		<cfquery name="local.getSimilarDiscussions" datasource="#$.site.getVar('datasource')#">
			select 
				hex(discussion.id) as id,				
				discussion.score,
				discussion.name,
				'#structKeyLIst(arguments.okwords)#' as words,
				ifNull(followers.counts,0) as follower_count,
				ifNull(messages.counts,0) as message_count
			from (
				select 
				discussion.id as id,
				discussion.name,
				(sum(0
					<cfloop collection="#arguments.okwords#" item="local.item">
						+if(discussion.name like '%#local.item#%',1,0)
					</cfloop>
				) / #local.denom#)
				+ 
				(sum(0
					<cfloop collection="#arguments.okwords#" item="local.item">
						+if(discussion.name like '%#local.item#%',1,0)
					</cfloop>
				) / (if(char_length(discussion.name) = 0, 1,char_length(discussion.name)) / 6))
				as score
				from discussion
				where discussion.active = 1
				and discussion.discussion_category_id = #$.database.uuid(arguments.discussionCategoryID)#
				and(
					0=1
					<cfloop collection="#arguments.okwords#" item="local.item">
						or discussion.name like '%#local.item#%'
					</cfloop>
				)
				group by discussion.id
			) discussion
			left join discussion_user on discussion_user.discussion_id = discussion.id
				and discussion_user.active = 1
				and discussion_user.user_id = #$.database.uuid(request.session.user.id)#
			left join (
				select discussion_user.discussion_id,
				count(discussion_user.id) as counts
				from discussion_user
				where discussion_user.active = 1
				group by discussion_user.discussion_id
			) followers on followers.discussion_id = discussion.id
			left join (
				select discussion_message.discussion_id,
				count(discussion_message.id) as counts
				from discussion_message
				where discussion_message.active = 1
				group by discussion_message.discussion_id
			) messages on messages.discussion_id = discussion.id
			where discussion.score > 0
				<!--- and discussion_user.id is null --->
			and messages.counts > 0
			order by discussion.score desc
			limit 5
		</cfquery>
		
		<cfreturn local.getSimilarDiscussions />
		
	</cffunction>
	
	<!--- --->

</cfcomponent>